﻿using Excel = Microsoft.Office.Interop.Excel;
using Sunny.UI.Win32;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms.DataVisualization.Charting;
using Microsoft.Office.Interop.Excel;
using Sunny.UI;
using System.Runtime.InteropServices;
using System.Data.Common;
using System.Xml.Linq;
using System.Reflection;
using System.Windows.Forms;

namespace NovoSerDes
{
    public class Custom_Excel : InterfaceExcel
    {
        private string excelFilePath = "";
        private string excelDirectoryPath = "";

        public Custom_Excel(string _excelFilePath) {
            excelFilePath = _excelFilePath;
            excelDirectoryPath = System.IO.Path.GetDirectoryName(excelFilePath);

            if (!System.IO.Directory.Exists(excelDirectoryPath))
            {
                System.IO.Directory.CreateDirectory(excelDirectoryPath);
            }
            if (!File.Exists(excelFilePath))
            {
                UIMessageDialog.ShowMessageDialog("找不到Excel文件,自动新建Excel文件", "提示", showCancelButton: false, UIStyle.Blue, showMask: false);
                // 创建一个新的Excel文件
                Excel.Application xlApp = new Excel.Application();
                Excel.Workbook xlBook = xlApp.Workbooks.Add(Type.Missing);
                xlBook.SaveAs(excelFilePath, Excel.XlFileFormat.xlOpenXMLWorkbook);
                // 关闭并释放资源
                xlBook.Close(false);
                xlApp.DisplayAlerts = false;
                xlApp.Quit();
                Marshal.ReleaseComObject(xlBook);
                Marshal.ReleaseComObject(xlApp);
                xlBook = null;
                xlApp = null;

                Console.WriteLine("已新建Excel文件");
            }
        }

        static public string OpenExcelFilePath()
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Title = "Select Excel File...";
            openFileDialog.Filter = "Excel Files (*.xlsx, *.xls)|*.xlsx;*.xls";
            string defaultOpenExcelDirectoryPath = Path.Combine(Environment.CurrentDirectory, "Excel FILE");
            if (!Directory.Exists(defaultOpenExcelDirectoryPath))
            {
                Directory.CreateDirectory(defaultOpenExcelDirectoryPath);
            }
            openFileDialog.InitialDirectory = Path.Combine(Environment.CurrentDirectory, "Excel FILE");
            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                string openExcelFilePath = openFileDialog.FileName;
                return openExcelFilePath;
            }
            else
            {
                return null;
            }
        }

        public void WriteStringToExcel(int Row, int Column, string wrDataString, int sheetIndex = 1)
        {
            //Application对象
            Excel.Application xlApp = new Excel.Application();
            //创建或打开工作簿Workbook
            Excel.Workbook xlBook = null;
            Excel.Worksheet xlSheet = null;

            try
            {
                //1、打开已有Excel文件，得到Workbook对象
                xlBook = xlApp.Workbooks.Open(excelFilePath);
                //2、新建一个Excel文件
                //xlBook = xlApp.Workbooks.Add(Type.Missing);

                //要操作的Sheet
                xlSheet = xlBook.Worksheets[sheetIndex];
                //写入数据
                xlSheet.Cells[Row, Column] = wrDataString; //或者xlSheet.Cells[Column][Row] = wrDataString;
                //1、保存excel文档
                xlBook.Save();
                //2、保存excel文档到指定路径
                //xlBook.SaveAs(excelFilePath, Excel.XlFileFormat.xlOpenXMLWorkbook);//.xlsx; Excel.XlFileFormat.xlWorkbookNormal是.xls
            }
            catch (Exception ex)
            {
                // 处理异常  
                Console.WriteLine("WriteStringToExcel发生错误: " + ex.Message);
            }
            finally
            {
                //关闭并释放资源
                xlBook.Close(false);
                xlApp.DisplayAlerts = false;
                xlApp.Quit();
                Marshal.ReleaseComObject(xlSheet);
                Marshal.ReleaseComObject(xlBook);
                Marshal.ReleaseComObject(xlApp);
                xlSheet = null;
                xlBook = null;
                xlApp = null;
            }
        }

        public void WriteUintToExcel(int Row, int Column, uint wrData, int sheetIndex = 1)
        {
            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlBook = null;
            Excel.Worksheet xlSheet = null;

            try
            {
                xlBook = xlApp.Workbooks.Open(excelFilePath);
                xlSheet = xlBook.Worksheets[sheetIndex];
                xlSheet.Cells[Row, Column] = wrData;
                xlBook.Save();
            }
            catch (Exception ex)
            {
                Console.WriteLine("WriteUintToExcel发生错误: " + ex.Message);
            }
            finally
            {
                xlBook.Close(false);
                xlApp.DisplayAlerts = false;
                xlApp.Quit();
                Marshal.ReleaseComObject(xlSheet);
                Marshal.ReleaseComObject(xlBook);
                Marshal.ReleaseComObject(xlApp);
                xlSheet = null;
                xlBook = null;
                xlApp = null;
            }
        }

        public void AddSheetAndRenameSheet(string sheetName)
        {
            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlBook = null;
            Excel.Worksheet xlSheet = null;

            try
            {
                xlBook = xlApp.Workbooks.Open(excelFilePath);
                Excel.Worksheet lastxlSheet = xlBook.Worksheets[xlBook.Worksheets.Count];
                xlSheet = xlBook.Worksheets.Add(Missing.Value, lastxlSheet, Missing.Value, Missing.Value);
                xlSheet.Name = sheetName;
                xlBook.Save();
            }
            catch (Exception ex)
            {
                Console.WriteLine("AddSheetAndRenameSheet发生错误: " + ex.Message);
            }
            finally
            {
                xlBook.Close(false);
                xlApp.DisplayAlerts = false;
                xlApp.Quit();
                Marshal.ReleaseComObject(xlSheet);
                Marshal.ReleaseComObject(xlBook);
                Marshal.ReleaseComObject(xlApp);
                xlSheet = null;
                xlBook = null;
                xlApp = null;
            }
        }

        public int PrintDtToExcel(System.Data.DataTable dtInput, int sheetIndex = 1, int startIndex = 1)
        {
            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlBook = null;
            Excel.Worksheet xlSheet = null;

            try
            {
                xlBook = xlApp.Workbooks.Open(excelFilePath);
                xlSheet = xlBook.Worksheets[sheetIndex];

                #region 列头内容
                // 列头内容  
                for (int j = 0; j < dtInput.Columns.Count; j++)
                {
                    xlSheet.Cells[startIndex, j + 1] = dtInput.Columns[j].ColumnName;

                    // 设置列宽为自动适应列头内容  
                    //xlSheet.Columns[j + 1].AutoFit(); // 注意索引从1开始
                }
                startIndex++;
                #endregion

                #region 表内容
                //表内容
                for (int i = 0; i < dtInput.Rows.Count; i++)
                {
                    for (int j = 0; j < dtInput.Columns.Count; j++)
                    {
                        xlSheet.Cells[(startIndex - 1) + i + 1, j + 1] = dtInput.Rows[i][j].ToString();
                    }
                }
                startIndex += dtInput.Rows.Count;
                #endregion

                #region 整个工作表格式
                // 设置整个工作表的格式
                Excel.Range wholeRange = xlSheet.Cells;
                wholeRange.Font.Name = "等线"; // 字体名称  
                wholeRange.Font.Size = 11; // 字体大小
                wholeRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //默认对齐方式为居中
                wholeRange.EntireColumn.AutoFit(); //调整工作表中列宽自动适应所有内容
                #endregion

                #region 列头格式
                // 列头格式
                Excel.Range headerRange = xlSheet.Rows[1] as Excel.Range;
                headerRange.Font.Bold = true; // 加粗  
                headerRange.Font.Name = "Times New Roman"; // 字体名称  
                headerRange.Font.Size = 10.5; // 字体大小  
                headerRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 水平居中
                // 设置填充颜色
                Excel.Range usedRange = xlSheet.UsedRange; // 获取工作表的已使用范围
                Excel.Range usedHeaderRow = usedRange.Rows[1]; // 获取UsedRange的第一行（列头）,注意索引是从1开始的，不是从0
                usedHeaderRow.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(91, 155, 213));
                // 设置边框
                Excel.Borders borders = usedHeaderRow.Borders;
                borders.LineStyle = Excel.XlLineStyle.xlContinuous;          
                borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;
                borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;
                //borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;
                //borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
                #endregion

                #region 表内容格式
                //Range range = xlSheet.Range["A4:A6"];
                //range.MergeCells = true; // 合并单元格
                #endregion

                xlBook.Save();
            }
            catch (Exception ex)
            {
                Console.WriteLine("PrintDtToExcel发生错误: " + ex.Message);
            }
            finally
            {
                xlBook.Close(false);
                xlApp.DisplayAlerts = false;
                xlApp.Quit();
                Marshal.ReleaseComObject(xlSheet);
                Marshal.ReleaseComObject(xlBook);
                Marshal.ReleaseComObject(xlApp);
                xlSheet = null;
                xlBook = null;
                xlApp = null;
            }
            return startIndex;
        }  
    }
}
